from django.test import TestCase
import datetime


# Create your tests here.
from ydsp.database.connection import Link_database


def dateRanges(beginDate, endDate):
    dates = []
    dt = datetime.datetime.strptime(beginDate, '%Y-%m-%d')
    date = beginDate[:]
    while date <= endDate:
        dates.append(date)
        dt = dt + datetime.timedelta(1)
        date = dt.strftime('%Y-%m-%d')
    return len(dates)


def sel_data():
    con, obj = Link_database()

    sql = """select j.na,j.director,j.nam,j.tube,k.name,j.user_id from user as k right join (select g.na,g.director,h.name as nam,g.tube,g.user_id from user as h right join (select e.name as na,f.director,f.tube,f.user_id from user as e right join (select d.director,d.tube,d.user_id from depar_user as d inner join (select b.director,b.tube from depar_user as b inner join depar_user as a on b.director=a.director group by b.tube) as c on d.tube=c.tube) as f on e.id=f.director) as g on h.id=g.tube) as j on k.id=j.user_id"""
    obj.execute(sql)
    q = obj.fetchall()

    return q


def deal_with():
    q = sel_data()
    director = []
    for i in q:
        if (i[0], i[1]) not in director:
            director.append((i[0], i[1]))
    # print(director)
    tube = []
    for i in q:
        if (i[2], i[3]) not in tube:
            tube.append((i[2], i[3]))
    # print(tube)
    tube_list = []
    for a in tube:
        d = {"tube": a[0], "id": a[1], "users": []}
        for i in q:
            if a[0] == i[2]:
                d['users'].append({
                    "user": i[4],
                    "id": i[5]
                })
        tube_list.append(d)
    # print(tube_list)

    director_list = []
    for i in director:
        d = {"director": i[0], "id": i[1], "tube": []}
        for c in q:
            if i[0] == c[0]:
                for b in tube_list:
                    if c[2] == b['tube'] and i[0] != c[2]:
                        d['tube'].append(b)
                        tube_list.remove(b)
        director_list.append(d)
    print(director_list)

    data = {
        "director": 1,
        "id": 1,
        "d": {
            "tube": 1,
            "id": 1,
            "t": {
                "user": 1,
                "id": 1
            }
        }
    }


if __name__ == '__main__':
    # s = 'tttt'
    # s.split('/')
    # x = [1, 2]
    # print(str(x), type(str(x)))
    # if s in x:
    # 	print ('#####')
    pass
    deal_with()


    # def test():
    #     a, b = 1, 1
    #     while True:
    #         yield b
    #         a, b = b, a + b
    #
    # obj = test()
    # for i in range(9):
    #     print(next(obj))